import traceback

import pymysql

#  多个 sn 以逗号,隔开 如： 1234,5678,0912
#  请输入订单号
#  只需要输入订单号就可以实现 在线支付
orderSns = '253510017108000'

# 固定参数
PAYTYPE = 4
PAYCHANNLEID = 3
APITYPE = 13


def payOrserBySql():
    # 参数校验
    isPass = _checkParams()
    if not isPass:
        print("参数校验不通过，程序自动退出")
        return
    # 准备基本参数
    # 使用 cursor() 方法创建一个游标对象 cursor
    db = pymysql.connect("db1.dev1.yiyao.cc", "mall_root", "20151118", "mall_order", port=13306, charset='utf8')
    cursor = db.cursor()
    userId, nickname, userSn = _prepareUserInfo(cursor, orderSns)
    print("查询到的买家信息是： ", userId, nickname, userSn)
    # 获取医药订单list信息
    medOrderList = _getMedOrderListBySplitOderSns(cursor, orderSns)
    if not medOrderList:
        print("查询到的医药订单list信息为空，程序自动退出")
        return
    print(f"查询到的医药订单list是： {medOrderList}")
    orderPayDetailList = []
    totalPayAmopunt = 0.0
    for medOrder in medOrderList:
        if medOrder:
            totalPayAmopunt += medOrder[2]
            ord = OrderPayDetail(medOrder[0], medOrder[1], medOrder[2], userId)
            orderPayDetailList.append(ord)

    orderPaySn = _generateOrderPaySn(cursor)
    orderpay = OrderPay(orderPaySn, userId, totalPayAmopunt)
    try:
        # 执行sql语句
        orderPayId = _saveOrderPay(cursor, orderpay)
        print("插入order_pay表完成")
        # 提交到数据库执行
        for orderPayDetail in orderPayDetailList:
            orderPayDetail.orderPayId = orderPayId
            _saveOrderPayDetail(cursor, orderPayDetail)
        print("插入order_pay_detail表完成")
        _updateMedOrder(cursor, orderSns)
        print("更新med_order表完成")
        print("全部执行完成，将要提交事务")
        db.commit()
    except Exception as e:
        traceback.print_exc()
        print(e)
        # 发生错误时回滚
        print("发生错误，事务回滚")
        db.rollback()
    finally:
        # 关闭数据库连接
        db.close()
    print("支付成功")


def _updateMedOrder(cursor, orderSns):
    updateMedOrderSql = f"update mall_order.med_order set order_status = 18 , pay_type = 1, pay_status = 1 , modify_time = now() , pay_time = now()  where order_sn = {orderSns}"
    print(f"【sql】 更新medOrder表支付状态的sql 是：{updateMedOrderSql}")
    cursor.execute(updateMedOrderSql)


def _saveOrderPayDetail(cursor, orderPayDetail):
    saveOrderPayDetailSql = f"insert into mall_order.order_pay_detail " \
                            f"(order_pay_id, order_id, order_sn, user_id, refund_amount, pay_amount, create_time, modify_time, STATUS, remark, has_valid ) " \
                            f"values ({orderPayDetail.orderPayId}, {orderPayDetail.orderId}, '{orderPayDetail.orderSn}', {orderPayDetail.orderId}, {orderPayDetail.refundAmount}, {orderPayDetail.payAmount}, " \
                            f"now(), now(), {orderPayDetail.status}, '{orderPayDetail.remark}', {orderPayDetail.has_valid})"
    print(f"【sql】 保存订单支付明细信息的sql 是：{saveOrderPayDetailSql}")
    cursor.execute(saveOrderPayDetailSql)


def _saveOrderPay(cursor, orderpay):
    saveOrderPaySql = f"insert into mall_order.order_pay " \
                      f"(order_pay_sn, pay_channel_id, user_id, pay_amount, real_pay_amount, status, pay_type, send_time, create_time, modify_time, version, has_refund, api_type, remark, trade_serial_num,has_valid) " \
                      f"values('{orderpay.orderPaySn}', {orderpay.payChannelId}, {orderpay.userId}, {orderpay.payAmount}, {orderpay.realPayAmount}, {orderpay.status},{orderpay.payType}," \
                      f"now(), now(), now(), {orderpay.version}, {orderpay.hasRefund}, {orderpay.apiType}, '{orderpay.remark}', '{orderpay.tradeSerialNum}', {orderpay.hasValid})"
    print(f"【sql】 保存订单支付信息的sql 是：{saveOrderPaySql}")
    cursor.execute(saveOrderPaySql)
    return cursor.lastrowid


def _getMedOrderListBySplitOderSns(cursor, orderSns):
    oderSnListStr = "','".join(orderSns.split(","))
    getMedOrderListSql = f"select sid, order_sn, deal_amount from med_order where order_sn in ('{oderSnListStr}')"
    print(f"【sql】 获取医药订单lsit的sql 是：{getMedOrderListSql}")
    cursor.execute(getMedOrderListSql)
    return cursor.fetchall()


def _generateOrderPaySn(cursor):
    generateOrderPaySnSql = "select CONCAT(now() -1, '1') as orderPaySn  from dual"
    print(f"【sql】 生成支付单号的sql 是：{generateOrderPaySnSql}")
    cursor.execute(generateOrderPaySnSql)
    return cursor.fetchone()[0]


def _prepareUserInfo(cursor, orderSns):
    orderSn = orderSns.split(",")[0]
    # 使用 execute()  方法执行 SQL 查询
    getUserInfoSql = f"select sid as userId , nickname, user_sn as userSn  from mall.web_user where sid = (select user_id  from med_order where order_sn  = {orderSn} limit 1 ) limit 1"
    print(f"【sql】 获取用户信息的sql 是：{getUserInfoSql}")
    cursor.execute(getUserInfoSql)
    return cursor.fetchone()


def _checkParams():
    if not orderSns:
        print("参数 orderSns 不能为空")
        return False
    return True


class OrderPayDetail:
    def __init__(self, medOrderId, orderSn, payAmount, userId):
        self.orderId = medOrderId
        self.orderSn = orderSn
        self.userId = userId
        # 0 是 无退款， 1 是有退款
        self.hasRefund = 0
        # 0 是 无效 ， 1 是 有效
        self.has_valid = 1
        # 支付状态 -1:失败 0:未支付 1:支付成功 2：支付中
        self.status = 1
        self.version = 0
        self.remark = 'sql模拟支付操作，支付成功'
        self.payAmount = payAmount
        self.refundAmount = 0

    def __str__(self) -> str:
        return super().__str__()


class OrderPay:
    def __init__(self, orderPaySn, userId, payAmount):
        self.orderPaySn = orderPaySn
        self.userId = userId
        self.payAmount = payAmount
        self.realPayAmount = payAmount
        self.refundAmount = 0
        self.status = 1
        self.payType = PAYTYPE
        self.payChannelId = PAYCHANNLEID
        self.version = 0
        self.hasRefund = 0
        self.apiType = APITYPE
        self.remark = 'sql模拟支付操作，支付成功'
        self.tradeSerialNum = orderPaySn
        self.hasValid = 1


if __name__ == "__main__":
    payOrserBySql()
